CREATE TABLE SCADAPoint
(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BreakerID INT NOT NULL FOREIGN KEY REFERENCES Asset(ID),
	Point VARCHAR(200) NOT NULL
)
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.Historian', 'None', 'None')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.PointQuery', 'SELECT NULL AS Point WHERE 1 IS NULL', 'SELECT NULL AS Point WHERE 1 IS NULL')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.QueryTolerance', '4.0', '4.0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.BreakerOpenValue', '0.0', '0.0')
GO

DROP VIEW ActiveSubscription
GO

CREATE VIEW ActiveSubscription AS
    SELECT
		UserAccountEmailID,
        UserAccountID,
        Approved,
        AssetGroup,
        EmailName,
        Category,
        EmailTypeID,
        Subject,
        LastSent,
        UserName,
        FirstName,
		LastName,
        Email,
        RequireApproval
	FROM (
        SELECT 
            UserAccountEmailType.ID AS UserAccountEmailID,
            UserAccountEmailType.UserAccountID,
            UserAccountEmailType.Approved,
            AssetGroup.Name AS AssetGroup,
            EmailType.Name AS EmailName,
            EmailCategory.Name AS Category,
            EmailType.ID AS EmailTypeID,
            SentEmail.Subject AS Subject,
            SentEmail.TimeSent AS LastSent,
            UserAccount.Name AS UserName,
            UserAccount.FirstName AS FirstName,
			UserAccount.LastName AS LastName,
            UserAccount.Email,
            EmailType.RequireApproval,
            ROW_NUMBER() OVER (
                PARTITION BY UserAccountEmailType.ID
                ORDER BY SentEmail.TimeSent DESC
            ) AS rn
        FROM UserAccountEmailType LEFT JOIN
            AssetGroup ON AssetGroup.ID = UserAccountEmailType.AssetGroupID LEFT JOIN
            EmailType ON UserAccountEmailType.EmailTypeID = EmailType.ID LEFT JOIN
            EmailCategory ON EmailCategory.ID = EmailType.EmailCategoryID LEFT JOIN
            SentEmail ON SentEmail.EmailTypeID = EmailType.ID LEFT JOIN
            UserAccount ON UserAccount.ID = UserAccountEmailType.UserAccountID
    ) AS recent WHERE rn = 1
GO